package com.mvc.business.service;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Service;

import com.mvc.base.model.Picture;
import com.mvc.base.service.BaseCompanyManager;
import com.mvc.base.service.CompanyManager;
import com.mvc.base.service.CustomerManager;
import com.mvc.base.service.PictureManager;
import com.mvc.base.service.UserCompanyManager;
import com.mvc.business.model.Order;
import com.mvc.business.model.OrderAssociated;
import com.mvc.business.model.OrderCharge;
import com.mvc.business.model.OrderItem;
import com.mvc.finance.model.FinancePrivilege;
import com.mvc.finance.service.FinancePrivilegeManager;
import com.mvc.framework.dao.HibernateIdUtils;
import com.mvc.framework.dao.HibernateWebUtils;
import com.mvc.framework.model.Auditable;
import com.mvc.framework.model.CommonAuditableEntity;
import com.mvc.framework.util.FrameworkContextUtils;
import com.mvc.framework.util.PageSearch;
import com.mvc.portal.service.CUserManager;

@Service
public class OrderManager extends BaseCompanyManager<Order, Integer> {
	@Autowired
	private OrderItemManager	    orderItemManager;
	@Autowired
	private CustomerManager	        customerManager;
	@Autowired
	private CompanyManager	        companyManager;

	@Autowired
	private OrderChargeManager	    orderChargeManager;

	@Autowired
	private PictureManager	        pictureManager;

	@Autowired
	private UserCompanyManager	    userCompanyManager;

	@Autowired
	private FinancePrivilegeManager	financePrivilegeManager;

	@Autowired
	private CUserManager	        cUserManager;

	public Order get(Serializable id) {
		Order order = super.get(id);
		if (order != null) {
			order.setCustomer(customerManager.getByObjectId(order.getCustomerId()));
			order.setCompany(companyManager.getByObjectId(order.getCompanyId()));
			order.setPictures(pictureManager.getPicturesByItemIdAndType(order.getObjectId(), Picture.PICTURE_OF_ORDER));
		}
		return order;
	}

	public Order getObject(Integer objectId) {
		Order order = super.getByObjectId(objectId);
		if (order != null) {
			order.setOrderItems(orderItemManager.getOrderItemsByOrderId(objectId));
			order.setCustomer(customerManager.getByObjectId(order.getCustomerId()));
			order.setCompany(companyManager.getByObjectId(order.getCompanyId()));
			order.setCharges(orderChargeManager.getOrderChargesByOrderId(objectId));
		}
		return order;
	}

	public Long getNeedAuditCount() {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		Query query = session.createQuery("select count(*) from " + Order.class.getName() + " where recStatus = ? and status= ?");
		query.setParameter(0, Auditable.REC_STATUS_VALID).setParameter(1, Order.ORDER_STATUS_COMMIT);
		List<Object> result = query.list();
		session.close();
		return (Long) result.get(0);
	}

	public void save(Order entity) {
		calculate(entity);
		if (null == entity.getId()) {
			Integer oldOrderId = entity.getObjectId();
			entity.setObjectId(HibernateIdUtils.getUnCacheCustomizeId(getHibernateTemplate().getSessionFactory(), "orderNo"));
			orderItemManager.updateOrderId(oldOrderId, entity.getObjectId());
			orderChargeManager.updateOrderId(oldOrderId, entity.getObjectId());
			pictureManager.updateOrderId(oldOrderId, entity.getObjectId());
		}
		super.save(entity);
	}

	public void updateTotal(Integer orderId) {
		Order order = getByObjectId(orderId);
		if (order != null) {
			calculate(order);
			super.save(order);
		}
	}

	protected void calculate(Order order) {
		if (order != null) {
			double total = 0D;
			double quantity = 0D;
			List<OrderItem> orderItems = orderItemManager.getOrderItemsByOrderId(order.getObjectId());
			for (OrderItem orderItem : orderItems) {
				total += (orderItem.getQuantity() * orderItem.getUnitPrice());
				quantity += orderItem.getQuantity();
			}
			List<OrderCharge> orderCharges = orderChargeManager.getOrderChargesByOrderId(order.getObjectId());
			for (OrderCharge orderCharge : orderCharges) {
				total += orderCharge.getCharge();
			}
			order.setQuantity(quantity);
			order.setTotal(total);
		}
	}

	public List<Order> getUnPayOrders() {
		String companyId = financePrivilegeManager.getCompanyIdsByUserId(FrameworkContextUtils.getCurrentUserId(),
		        FinancePrivilege.TYPE_RECEIVEABLE);
		String sql = "select A from " + Order.class.getName() + " A where A.recStatus=? and A.status>=? and A.financeStatus=?";
		if (companyId == null) {
			return searchBySql(sql, new Object[] { CommonAuditableEntity.REC_STATUS_VALID, Order.ORDER_STATUS_APPROVED,
			        Order.ORDER_FINANCE_STATUS_UNPAY });
		} else if (companyId.length() > 0) {
			sql += " and companyId in(" + companyId + ")";
			return searchBySql(sql, new Object[] { CommonAuditableEntity.REC_STATUS_VALID, Order.ORDER_STATUS_APPROVED,
			        Order.ORDER_FINANCE_STATUS_UNPAY });
		} else {
			return new ArrayList<Order>();
		}
	}

	public List<Order> getNeedPayOrder(Date noticeDate) {
		StringBuilder querySql = new StringBuilder(200);
		querySql.append("select A from ").append(Order.class.getName()).append(" A where A.financeStatus != ?")
		        .append(" AND A.predictPayDate<? AND A.recStatus = ? AND A.status >= ?").append(" AND A.status < ?");
		return searchBySql(querySql.toString(), new Object[] { Order.ORDER_FINANCE_STATUS_PAYED, noticeDate, Auditable.REC_STATUS_VALID,
		        Order.ORDER_STATUS_APPROVED, Order.ORDER_STATUS_FINISHED });
	}

	public List<Order> getAllOrder() {
		StringBuilder querySql = new StringBuilder(200);
		querySql.append("select A from ").append(Order.class.getName()).append(" A where A.recStatus = ? AND A.status >= ?")
		        .append(" AND A.status <= ?");
		return searchBySql(querySql.toString(), new Object[] { Auditable.REC_STATUS_VALID, Order.ORDER_STATUS_APPROVED,
		        Order.ORDER_STATUS_FINISHED });
	}

	public void financeFind(final PageSearch page) {
		final Integer userId = FrameworkContextUtils.getCurrentUserId();
		getHibernateTemplate().execute(new HibernateCallback<PageSearch>() {
			public PageSearch doInHibernate(Session session) {
				Criteria criteria = HibernateWebUtils.createCriteria(session, page.getEntityClass(), page.getFilters());
				if (userId != null) {
					criteria.add(Restrictions.sqlRestriction("company_Id in (select B.company_Id from B_FINANCE_PRIVILEGE B"
					        + " where B.user_id = " + userId + " and B.type= " + FinancePrivilege.TYPE_RECEIVEABLE + ")"));
				}
				if (0 == page.getTotalCount()) {
					criteria.setProjection(Projections.rowCount());
					Object object = criteria.uniqueResult();
					int total = object == null ? 0 : ((Number) object).intValue();
					page.setTotalCount(total);
				}
				addOrder(page, criteria);
				criteria.setProjection(null);
				List items = criteria.setFirstResult(page.getBegin()).setMaxResults(page.getPageSize()).list();
				page.setList(items);
				return page;
			}
		});
	}

	public void findForCommercialInvoiceImport(final PageSearch page) {
		final Integer userId = FrameworkContextUtils.getCurrentUserId();
		getHibernateTemplate().execute(new HibernateCallback<PageSearch>() {
			public PageSearch doInHibernate(Session session) {
				Criteria criteria = HibernateWebUtils.createCriteria(session, page.getEntityClass(), page.getFilters());
				if (userId != null) {
					criteria.add(Restrictions.sqlRestriction("company_Id in (select B.company_Id from B_USER_COMPANY B"
					        + " where B.user_id = " + userId + ")"));
				}
				criteria.add(Restrictions
				        .sqlRestriction("exists (select 1 from b_order_item a where a.rec_status = 1 and a.order_id >0 and a.order_id = this_.object_id and (not exists ("
				                + " select 1 from b_commercial_invoice_item c where c.rec_status = 1 and c.commercial_invoice_id >0 and c.order_item_id = a.object_id ) "
				                + " or exists (select 1 from (select c.order_item_id,sum(c.quantity) quantity from b_commercial_invoice_item c where c.rec_status = 1 and c.commercial_invoice_id >0 group by c.order_item_id) c where  c.order_item_id = a.object_id and c.quantity < a.quantity)))"));
				if (0 == page.getTotalCount()) {
					criteria.setProjection(Projections.rowCount());
					Object object = criteria.uniqueResult();
					int total = object == null ? 0 : ((Number) object).intValue();
					page.setTotalCount(total);
				}
				addOrder(page, criteria);
				criteria.setProjection(null);
				List items = criteria.setFirstResult(page.getBegin()).setMaxResults(page.getPageSize()).list();
				page.setList(items);
				return page;
			}
		});
	}

	public void findForPackingImport(final PageSearch page) {
		final Integer userId = FrameworkContextUtils.getCurrentUserId();
		getHibernateTemplate().execute(new HibernateCallback<PageSearch>() {
			public PageSearch doInHibernate(Session session) {
				Criteria criteria = HibernateWebUtils.createCriteria(session, page.getEntityClass(), page.getFilters());
				if (userId != null) {
					criteria.add(Restrictions.sqlRestriction("company_Id in (select B.company_Id from B_USER_COMPANY B"
					        + " where B.user_id = " + userId + ")"));
				}
				criteria.add(Restrictions
				        .sqlRestriction("exists (select 1 from b_order_item a where a.rec_status = 1 and a.order_id >0 and a.order_id = this_.object_id and (not exists ("
				                + " select 1 from b_packing_item c where c.rec_status = 1 and c.packing_id >0 and c.order_item_id = a.object_id ) "
				                + " or exists (select 1 from (select c.order_item_id,sum(c.quantity) quantity from b_packing_item c where c.rec_status = 1 and c.packing_id >0 group by c.order_item_id) c where c.order_item_id = a.object_id and c.quantity < a.quantity)))"));
				if (0 == page.getTotalCount()) {
					criteria.setProjection(Projections.rowCount());
					Object object = criteria.uniqueResult();
					int total = object == null ? 0 : ((Number) object).intValue();
					page.setTotalCount(total);
				}
				addOrder(page, criteria);
				criteria.setProjection(null);
				List items = criteria.setFirstResult(page.getBegin()).setMaxResults(page.getPageSize()).list();
				page.setList(items);
				return page;
			}
		});
	}

	public List<Order> getAssociatesOrders(Integer associatedId, Integer type) {
		StringBuilder querySql = new StringBuilder(200);
		querySql.append("select A from ").append(Order.class.getName()).append(" A,").append(OrderAssociated.class.getName())
		        .append(" B where A.recStatus = ?").append(" AND A.objectId = B.orderId and B.associatedId=? and type=?");
		return searchBySql(querySql.toString(), new Object[] { Auditable.REC_STATUS_VALID, associatedId, type });
	}

	public String getAssociatesOrderClientPoNo(Integer associatedId, Integer type) {
		List<Order> orders = getAssociatesOrders(associatedId, type);
		if (orders.size() > 0) {
			StringBuilder querySql = new StringBuilder(200);
			for (Order order : orders) {
				if (StringUtils.isNotBlank(order.getClientPo())) {
					querySql.append(order.getClientPo()).append(", ");
				}
			}
			if (querySql.length() > 0) {
				return querySql.substring(0, querySql.length() - 2);
			}
		}
		return "";
	}

	public boolean canBackward(Integer orderId) {
		StringBuilder querySql = new StringBuilder(200);
		querySql.append("select count(*) from ").append(OrderAssociated.class.getName())
		        .append(" B where B.orderId=? and type!=? and associatedId>?");
		Long count = searchObjectBySql(Long.class, querySql.toString(), new Object[] { orderId, OrderAssociated.TYPE_BANK_STATEMENT, 0 });
		return count == 0;
	}

	public List<Object> getCount(Integer companyId, Date beginDate, Date endDate) {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		Query query = session.createQuery("select count(*) from " + Order.class.getName()
		        + " where recStatus = ? and status>= ? and companyId=? and auditDate >= ? and auditDate <= ? ");
		query.setParameter(0, Auditable.REC_STATUS_VALID).setParameter(1, Order.ORDER_STATUS_APPROVED).setParameter(2, companyId)
		        .setParameter(3, beginDate).setParameter(4, endDate);
		List<Object> result = query.list();
		session.close();
		return result;
	}

	public List<Object[]> summary(Integer companyId, Date beginDate, Date endDate) {
		Session session = getHibernateTemplate().getSessionFactory().openSession();
		Query query = session.createQuery("select currency,sum(total) from " + Order.class.getName()
		        + " where recStatus = ? and status>= ? and companyId=? and auditDate >= ? and auditDate <= ? group by currency");
		query.setParameter(0, Auditable.REC_STATUS_VALID).setParameter(1, Order.ORDER_STATUS_APPROVED).setParameter(2, companyId)
		        .setParameter(3, beginDate).setParameter(4, endDate);
		List<Object[]> result = query.list();
		session.close();
		return result;
	}

}
